﻿package com.webdesign.dao.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.webdesign.dto.CountDTO;

public class TbUserhomeworkSQL {
	
	/*
	 * 	取出tb_userhomework表格中state为1或2的记录的条数，返回的countDTO的信息为每个homeworkid对应的state为1或2的记录条数
	 */
	public List<CountDTO> getCount(String termid){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String strs = null;
		ResultSet rs1 = null;
		List<CountDTO> counts = new ArrayList<CountDTO>();
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select HomeworkID,count(State) as num from tb_userhomework where State in('1','2') and UserhomeworkID like'"+termid+"%' group by HomeworkID order by HomeworkID desc");
			while (rs.next()) {
				CountDTO count = new CountDTO();
				count.setHomeworkid(rs.getString("HomeworkID"));
				count.setFinishnum(rs.getInt("num"));
				counts.add(count);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//System.out.println(counts.get(0).getHomeworkid()+"-->"+counts.get(0).getFinishnum());
		
		return counts;
	}

	public Integer getUserhomeworkid(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Integer count = 0;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from tb_userhomework");
			// 将光标移动到最后一行     
			rs.last(); 
			// 得到当前行号，即结果集记录数  
			count = rs.getRow(); 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
        }
	/*
	 * 获取tb_userhomework中userhomeworkid的最大值
	 */
	public String getMaxUserhomeworkid(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String strs = null;
		Integer maxId = 0;
		String str = null;
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select UserHomeworkID as UserHomeworkID from tb_userhomework");
			while (rs.next()) {
				strs=rs.getString("UserHomeworkID");
				str = strs.split("_")[1];
				if(Integer.parseInt(str) > maxId){
					maxId = Integer.parseInt(str);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return maxId.toString();
	}
	
}

